package com.scuec.trainingmanagementsys.mapper;

import com.scuec.trainingmanagementsys.entity.Announcement;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface AnnouncementMapper {

    // 新增公告
    @Insert("INSERT INTO announcement (AnnouncementTitle, AnnouncementContent, TeacherID, ProjectID, ReleaseTime) " +
            "VALUES (#{announcementTitle}, #{announcementContent}, #{teacherID}, #{projectID}, NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "announcementID")
    int addAnnouncement(Announcement announcement);

    // 整表查询（关联教师表和项目表）
    @Select("SELECT " +
            "a.*, " +
            "t.TeacherName, " +
            "p.ProjectName " +
            "FROM announcement a " +
            "LEFT JOIN teacher t ON a.TeacherID = t.TeacherID " +
            "LEFT JOIN project p ON a.ProjectID = p.ProjectID")
    List<Announcement> findAll();

    // 按标题模糊查询（关联教师表和项目表）
    @Select("SELECT " +
            "a.*, " +
            "t.TeacherName, " +
            "p.ProjectName " +
            "FROM announcement a " +
            "LEFT JOIN teacher t ON a.TeacherID = t.TeacherID " +
            "LEFT JOIN project p ON a.ProjectID = p.ProjectID " +
            "WHERE a.AnnouncementTitle LIKE CONCAT('%', #{title}, '%')")
    List<Announcement> findByTitle(@Param("title") String title);

    // 单条删除（按公告标题）
    @Delete("DELETE FROM announcement WHERE AnnouncementTitle = #{title}")
    int deleteByTitle(String title);

    // 批量删除（按公告标题列表）
    @DeleteProvider(type = AnnouncementSqlProvider.class, method = "deleteByTitlesSql")
    int deleteByTitles(@Param("titles") List<String> titles);

    // 根据教师的teacherNumber查询公告
    @Select("SELECT " +
            "a.*, " +
            "t.TeacherName, " +
            "p.ProjectName " +
            "FROM announcement a " +
            "LEFT JOIN teacher t ON a.TeacherID = t.TeacherID " +
            "LEFT JOIN project p ON a.ProjectID = p.ProjectID " +
            "WHERE t.TeacherNumber = #{teacherNumber}")
    List<Announcement> findByTeacherNumber(@Param("teacherNumber") String teacherNumber);

    // SQL 动态生成类（批量删除用）
    class AnnouncementSqlProvider {
        public String deleteByTitlesSql(List<String> titles) {
            StringBuilder sql = new StringBuilder("DELETE FROM announcement WHERE AnnouncementTitle IN (");
            for (int i = 0; i < titles.size(); i++) {
                sql.append("#{titles[").append(i).append("]}");
                if (i < titles.size() - 1) {
                    sql.append(",");
                }
            }
            sql.append(")");
            return sql.toString();
        }
    }
}